rttest2fandomcom-20200214-history
PgToMySQL
Tool is available at http://fsck.com/pub/rt/contrib/3.0/Conversion/ It's a small tool JesseVincent built for a client who wanted to move from Pg to Mysql with their existing RT3 database. You should start with a full RT database on postgres and an RT3 database on mysql that contains only the schema and not the database content. ---- I wasn't aware of the above tool so I did it by hand successfully. Here are my notes: Did a test dump of the existing rt with 'pg_dump -aD -U rt_user -f /tmp/rt.sql rt3' The output looks fairly clean so I'm snapshotting my test machine, 'delete from "// To replace column names with non-quoted versions. I had to go through that a bunch of times. Note that there's no trailing /g on the sub commands. Only the first match should be the column name. Delete my rows again, reimport. me@vm-me:/tmp$ mysql -u rtuser -p rtdb < /tmp/rt.mysql Enter password: ERROR 1305 (42000) at line 1902214: FUNCTION pg_catalog.setval does not exist I removed all the SELECT lines that called the pg_catalog.setval function. I believe these are for autoincrement type fields. Hopefully MySQL can live without them. Delete my rows and reimport. This completed successfully and produced an RT that I can log into. Awesome. I was even able to create a ticket. This sort of implies that removing the setvals didn't prevent MySQL from handling the autoinc id fields. So, not only is it possible to migrate all the data from Postgres to MySQL, it's merely tedious and annoying rather than a huge ordeal. ---- we had some problems with your tool: broken german umlauts, dashboard was not migrated so we added some lines: $old_handle->{pg_enable_utf8}; $new_handle->do("SET NAMES 'utf8'"); my @tables = qw/ Users ACL Attachments CachedGroupMembers CustomFieldValues CustomFields FM_Articles FM_Classes FM_ObjectTopics FM_Topics GroupMembers Groups Links ObjectCustomFields ObjectCustomFieldValues Principals Queues QueueDeactivatedScrips ScripActions ScripConditions Scrips Templates Tickets Transactions Attributes/; ;' for all tables, then try to suck in this file. Postgres apparently uses all lower case table/field names and/or is case insensitive. MySQL uses camel case and is case sensitive. I did the following to convert the table names in the INSERT statements: (for table in `sudo ls /var/lib/mysql/rtdb/|grep frm|sed -e 's/.frm//'`; do THISLC=`echo ${table}|tr A-Z a-z`; echo "-e 's/INSERT INTO ${THISLC}/INSERT INTO ${table}/g'"; done)|xargs sed rt.sql > rt.mysql The above assumes icky MyISAM tables. I then had to hack out the SET lines at the beginning of the file. me@vm-me:/tmp$ mysql -u rtuser -p rtdb < /tmp/rt.mysql Enter password: ERROR 1153 (08S01) at line 1085459: Got a packet bigger than 'max_allowed_packet' bytes That is a mysql setting. Let's bump it up. It's set to 16M... 128M should do the trick =D. Let's delete the imported rows and try again: (for i in `sudo ls /var/lib/mysql/rtdb/|grep frm|sed -e 's/.frm//g'`; do echo "delete from ${i};"; done)|mysql -u rtuser -p rtdb The above also assumes MyISAM tables. me@vm-me:/tmp$ mysql -u rtuser -p rtdb < /tmp/rt.mysql Enter password: ERROR 1064 (42000) at line 1770325: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"type", localtarget, localbase, lastupdatedby, lastupdated, creator, created) VA' at line 1 I removed the quotes around the 'type' column name for a handful of lines. Delete all the rows again and reimport. I get more column name fun: me@vm-me:/tmp$ mysql -u rtuser -p rtdb < /tmp/rt.mysql Enter password: ERROR 1064 (42000) at line 1783872: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"domain", "type", instance) VALUES (2, 'User 1', 'ACL equiv. for user 1', 'ACLEq' at line 1 This is getting boring. Started doing this in vi: /INSERT.\{1,\}".\{1,\}VALUES :.,$s/"